﻿﻿using System;
using System.Data;
using System.Configuration;
using Oracle.DataAccess.Client;

namespace Wicresoft.EKA.AccountInfoUpdateApp.Init
{
    /// <summary>
    /// 数据库通用操作类
    /// </summary>
    public class OracleDataAccess : IDisposable
    {
        private readonly static string connstr = ConfigurationManager.ConnectionStrings["Context"].ConnectionString;
        //private readonly static string connstr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.24.143.198)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=FJYC)));User Id=ADMIN;Password=asb#1234;";
        public OracleConnection conn { get; private set; }
        public OracleDataAccess()
            : this(connstr)
        {
        }
        /// <summary>
        /// 带连接字符串的构造函数
        /// </summary>
        /// <param name="constr"></param>
        public OracleDataAccess(string constr)
        {
            conn = new OracleConnection(constr);
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            //打开数据库连接
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                try
                {
                    if (conn.State == ConnectionState.Broken)
                    {
                        //先关闭，然后打开数据库连接
                        conn.Close();
                    }
                    //打开数据库连接
                    conn.Open();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            //判断连接的状态是否已经打开
            if (conn.State == ConnectionState.Open || conn.State == ConnectionState.Broken)
            {
                conn.Close();
            }
        }
        /// <summary>  
        /// 执行查询语句，并返回查询结果集中第一行的第一列（object类型）。如果找不到结果集中第一行的第一列，则返回 null 引用
        /// </summary>  
        /// <param name="sql">SQL查询语句</param>  
        /// <returns></returns>  
        public object ExecuteScalar(string sql, params OracleParameter[] cmdParms)
        {
            return this.ExecuteScalar(sql, null, cmdParms);
        }
        /// <summary>  
        /// 执行查询语句，并返回查询结果集中第一行的第一列（object类型）。如果找不到结果集中第一行的第一列，则返回 null 引用
        /// </summary>  
        /// <param name="sql">SQL语句串</param>  
        /// <param name="sql">SQL事务</param>  
        /// <param name="cmdParms">SQL查询语句的参数</param>  
        /// <returns></returns>  
        public object ExecuteScalar(string sql, OracleTransaction tran, params OracleParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            this.PrepareCommand(cmd, conn, tran, CommandType.Text, sql, cmdParms);
            object result = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return result;
        }
        /// <summary>  
        /// 执行查询语句，返回 OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )  
        /// </summary>  
        /// <param name="sql">SQL查询语句</param>  
        /// <returns></returns>  
        public OracleDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, null);
        }
        /// <summary>  
        /// 执行查询语句，返回 OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )  
        /// </summary>  
        /// <param name="sql">SQL查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns> 
        public OracleDataReader ExecuteReader(string sql, params OracleParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            this.PrepareCommand(cmd, conn, null, CommandType.Text, sql, cmdParms);
            OracleDataReader myReader = cmd.ExecuteReader();
            return myReader;
        }
        /// <summary>  
        /// 执行不带参数的SQL语句 
        /// </summary>  
        /// <param name="sql">SQL命令语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteCommand(string sql)
        {
            OracleTransaction tran = null;
            return ExecuteCommand(sql, tran, null);
        }
        /// <summary>  
        /// 使用事务执行不带参数的SQL语句 
        /// </summary>  
        /// <param name="sql">SQL命令语句</param>
        /// <returns>影响的记录数</returns>
        /// <summary>  
        /// 执行SQL语句，返回影响的记录数  
        /// </summary>  
        /// <param name="sql">SQL命令语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>影响的记录数</returns>  
        public int ExecuteCommand(string sql, params OracleParameter[] cmdParms)
        {
            return this.ExecuteCommand(sql, null, cmdParms);
        }
        /// <summary>  
        /// 执行SQL语句，返回影响的记录数  
        /// </summary>  
        /// <param name="sql">SQL命令语句</param>
        /// <param name="tran">事务</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>影响的记录数</returns>  
        public int ExecuteCommand(string sql, OracleTransaction tran, params OracleParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            try
            {
                this.PrepareCommand(cmd, conn, tran, CommandType.Text, sql, cmdParms);
                int rowsAffected = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return rowsAffected;
            }
            catch (OracleException e)
            {
                throw e;
            }
        }
        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataSet 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns></returns>
        public DataSet ExecuteFillToDataSet(string sql)
        {
            return ExecuteFillToDataSet(sql, null);
        }
        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataSet 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        public DataSet ExecuteFillToDataSet(string sql, params OracleParameter[] cmdParms)
        {
            this.Open();//打开数据连接
            OracleCommand cmd = new OracleCommand();
            this.PrepareCommand(cmd, conn, null, CommandType.Text, sql, cmdParms);
            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }
        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataTable 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns></returns>
        public DataTable ExecuteFillToDataTable(string sql)
        {
            return ExecuteFillToDataSet(sql).Tables[0];
        }
        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataTable 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        public DataTable ExecuteFillToDataTable(string sql, params OracleParameter[] cmdParms)
        {
            return ExecuteFillToDataSet(sql, cmdParms).Tables[0];
        }

        private void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            command.Connection = connection;
            command.CommandText = cmdText;
            command.CommandType = cmdType;
            command.BindByName = true;
            if (trans != null) command.Transaction = trans;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                {
                    if (parm != null)
                    {
                        if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && parm.Value == null)
                        {
                            parm.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parm);
                    }
                }
            }
        }
        /// <summary>
        /// 实现IDisposable
        /// </summary>
        public void Dispose()
        {
            this.Close();
        }
        /// <summary>
        /// 析构函数
        /// </summary>
        ~OracleDataAccess()
        {
            this.Close();
        }
    }
}
